Rwanda Soil Health Study - Round 1

dataDir <- normalizePath(file.path("..", "..", "data"))
forceUpdateAll <- FALSE

1 Objectives

The objectives of this notebook are to analyze the results from the first follow up round of the Rwanda long term soil health study.

2 Key Takeaways

Coming soon!

3 Data Prep

I’m going to load the baseline data from the baseline analysis. The report and data can be found here. I’ll load the new data directly from CommCare. The original baseline data object was d but I’m going to make it b. Each subsequent round will be r1, r2 and so on.

Overall I want to bring in 3 data sources:

• Basline survey data and soil data
• Round 1 survey and and soil data from 16B
• Round 1 yield and soil data - these data come from paired climbing bean harvest measurements and soil samples from 16B
• We can also look at maize paired yield and soil samples from 17A.

3.1 Baseline data

baselineDir <- normalizePath(file.path("..", "rw_baseline", "data"))
load(file=paste0(baselineDir, "/shs rw baseline full soil.Rdata")) # obj d
b <- baseVars

Context point: The baseline data has 2439 rows. This is 9 fewer rows than we expected in the baseline. This is because of some farmers not being surveyed as expected. See the baseline report for more details. Also, these baesline values have te

Alex Villec wrote a cleaning script to deal with the first round of Rwanda SHS follow up data and make key adjustments to the data. To utilize that do file here, I’m going to download the data from Commcare, save it, and have the dofile access that file to execute. However, the original file Alex was using had different variable names than the file pulled by the API. The options from here are to just go with the file from Alex or to align the variable names between his version and the CC version. It’s valuable to have the data directly from CC but it’ll involve more work up front

3.2 Round 1 data

source("../oaflib/commcareExport.R")
r <- getFormData("oafrwanda", "M&E", "16B Ubutaka (Soil)", forceUpdate = forceUpdateAll)

[1] "found fdd434a62c6512b320a4cb8c4fb872a"

write.csv(r, file="rawCcR1Data.csv", row.names = F)

3.3 Yield data

yp <- getFormData("oafrwanda", "M&E", "16B ALL Isarura (Harvest)", forceUpdate = forceUpdateAll)

[1] "found 736b25426bb4f9320a07d9c42b738ea"

write.csv(yp, file="rawCcYpData.csv", row.names=F)

The first round of data from CommCare has 2381 observations. This leaves XX number of farmers unsurveyed in the first survey round. See this cleaning file for more information on the farmers we did not find again in the first follow up.

Here I’m going to call the STATA cleaning file to make AV’s changes to the R1 follow up data. This requires that the data from CC have the same variable names as the STATA cleaning file. I’m going to try to execute that here:

stataDir <- normalizePath(file.path("..", "rw_round_1_check"))

Here I access the soil predictions from the OAF soil lab. Patrick Bell manages the lab and Mike Barber oversees the prediction scripts.

soilDir <- normalizePath(file.path("..", "..", "OAF Soil Lab Folder", "Projects", "rw_shs_second_round", "4_predicted", "other_summaries"))
soil <- read.csv(file=paste(soilDir, "combined-predictions-including-bad-ones.csv", sep = "/"))

3.4 Combine baseline and R1

Combine the available data by farmer and resolve merging issues. These data can be combined long as long as the variable names are consistent or wide. I’m going to combine the data long and use split type commands to aggregate the data more easily. Confirm the variable names are consistent. By advancing this code on 5/9/17, I’m for the time being ignoring the cleaning Alex did in his do file. I’ll need to go back and incorporate those changes.

TODO: see if the variables names in Alex’s raw data, shared by Nathaniel, match the data I’m downloading from commcare. If so, don’t use the var_names.xlsx sheet and instead use those variable names and Alex’s do file to preserve all of his changes.

Not many of the names are the same. I’ve downloaded the meta data from CommCare which I’ll use to simplify the cleaning of the round 1 data. I’m also going to reshape the baseline variable names to simplify the matching of baseline variables to round 1 variables.

datNames <- function(dat){
  varNames = names(dat)
  exVal = do.call(rbind, lapply(varNames, function(x){
    val = dat[1:3,x]
    return(val)
  }))
  
  out = cbind(varNames, exVal)
  return(out)
}
baseNames <- datNames(b)
write.csv(baseNames, file="baseline var names.csv", row.names = F)

Load Alex’s raw data and take the variable names from this. If I can align these variable names with the data from CC I can then execute Alex’s cleaning script on the CC data and proceed with combining the data

3.5 Stata .do file

rawDir <- normalizePath(file.path("Soil health study (year one)", "data"))
avRaw <- read.csv(paste(rawDir, "y1_shs_rwanda_28sep.csv", sep = "/"), stringsAsFactors = F)

It looks like the data from CommCare aligns with the raw data Alex worked with at info_formid which is the second index for avRaw and the 10th index for r. Let’s just try transferring them over and the work of updating the variable names through the CC codebook export may not be necessary!

varTest <- data.frame(fromcc = names(r)[10:409], fromav = names(avRaw)[2:401])
# head(varTest)
# tail(varTest)
#varTest[90:120,]

It seems to line up okay (with some adjustments)! To incorporate Alex’s cleaning code I have to export the data from R to a form Stata accept, run the code, and then load the data back in.

This function will remove all strange outputs from the data from CommCare so that the STATA code works

charClean <- function(df){
  
  df <- as.data.frame(lapply(df, function(x){
  x = gsub("'", '', x)
  x = gsub("^b", '', x)
  x = ifelse(grepl("map object", x)==T, NA, x)
  return(x)
  }))
return(df)
}
r <- charClean(r)

names(r)[10:409] <- names(avRaw)[2:401]
#export so stata can run - check for variable names longer than 32char
table(nchar(names(r)))

 2  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 32 33 34 36 37 38 39 40 41 42 43 44 45 46 47 48 49 51 52 
 1  4  3  1  1  2  6  1  1  2  3  5 17 11 16 12  5  8  1  7  1  3  9  9  3  7  2  3  1 28 16 47 32 11  7 27 18 21 31 10  7  4  3  1  1 

write.csv(r, file="toBeCleanedStata.csv", row.names = F)
stata("cleans_y1_shs_rwanda.do", stata.echo=F)

Now load the result of the Stata file

r <- read.csv("cleanedforR.csv", stringsAsFactors = F)

newName <- read_excel("var_names.xlsx", sheet=1)

qTypes <- c("Multiple Choice", "Phone Number or Numeric ID", "Checkbox", "Text", "Decimal", "Image Capture", "Barcode Scan", "GPS", "Integer", "Time", "Date")

newName <- newName %>% dplyr::select(1:6
) %>% dplyr::filter(newName$Type %in% qTypes) %>% as.data.frame()

#newName <- newName %>% filter(new.var.name!="general.comment")
metaVars <- names(r)[1:10]
newNameVars <- c(metaVars, newName$new.var.name)

length(newNameVars)==dim(r)[2]

write.csv(newNameVars, file="newVar check.csv")
write.csv(names(r), file="round1 Var check.csv")

names(r) <- newNameVars

# drop vars with drop
r <- r[,-which(grepl("drop.", names(r)))]

qNum <- c("Phone Number or Numeric ID", "Decimal", "Integer")
nums <- newName[newName$Type %in% qNum, "new.var.name"]
nums <- nums[-which(grepl("drop.", nums))]

toRemove <- c("phone", "oafid")
nums <- nums[!nums %in% toRemove]

# add in plot.size
#nums <- c(nums, "plot.size")

r[, nums] <- as.data.frame(lapply(r[,nums], function(x){
  as.numeric(as.character(x))
}))

4 Cleaning

The r dataframe has many more variables than the baseline survey. This was in part expected; we added questions to the first follow up round based on lessons from the baseline. It’s also due to how the survey was set up in CommCare. Before combining the baseline and the first follow up round I need to:

• reshape the round 1 variables so that they appropriately match the baseline variables
• Clean those variales or prepare them as need be for a
• For variables with no match, clean

multiplot <- function(..., plotlist=NULL, file, cols=1, layout=NULL) {
  library(grid)
  # Make a list from the ... arguments and plotlist
  plots <- c(list(...), plotlist)
  numPlots = length(plots)
  # If layout is NULL, then use 'cols' to determine layout
  if (is.null(layout)) {
    # Make the panel
    # ncol: Number of columns of plots
    # nrow: Number of rows needed, calculated from # of cols
    layout <- matrix(seq(1, cols * ceiling(numPlots/cols)),
                    ncol = cols, nrow = ceiling(numPlots/cols))
  }
 if (numPlots==1) {
    print(plots[[1]])
  } else {
    # Set up the page
    grid.newpage()
    pushViewport(viewport(layout = grid.layout(nrow(layout), ncol(layout))))
    # Make each plot, in the correct location
    for (i in 1:numPlots) {
      # Get the i,j matrix positions of the regions that contain this subplot
      matchidx <- as.data.frame(which(layout == i, arr.ind = TRUE))
      print(plots[[i]], vp = viewport(layout.pos.row = matchidx$row,
                                      layout.pos.col = matchidx$col))
    }
  }
}

4.1 Drop variables

toDrop <- c("appformid", "id", "domain", "metadatadeviceid")
r <- r[,!names(r) %in% toDrop]

source("../oaflib/misc.R")
names(r) <- gsub("^y1_|intro_", "", names(r))
r[r=="."] <- NA
r <- divideGps(r, "gps_coord")

4.2 Categorical variables

The responses of the categorical variables should be regulated through CC, however, to check, make a table that shows the top ten responses in descending order and make a graph of response counts to know what to check. I’ll then capture any characters that should be numeric and convert them.

catVars <- names(r)[sapply(r, function(x){
  is.character(x)
})]
enumClean <- function(dat, x, toRemove){
  dat[,x] <- ifelse(dat[,x] %in% toRemove, NA, dat[,x])
  return(dat[,x])
}
strTable <- function(dat, x){
  varName = x
  tab = as.data.frame(table(dat[,x], useNA = 'ifany'))
  tab = tab[order(tab$Freq, decreasing = T),]
  end = ifelse(length(tab$Var1)<10, length(tab$Var1), 10)
  repOrder = paste(tab$Var1[1:end], collapse=", ")
  out = data.frame(variable = varName,
                   responses = repOrder)
  
  return(out)
}
# clean up known values
catEnumVals <- c("-99", "-88", "- 99", "-99.0", "88", "_88", "- 88", "0.88",
                 "--88", "__88", "-88.0", "99.0")
r[,catVars] <- sapply(catVars, function(y){
  r[,y] <- enumClean(r,y, catEnumVals)
})
responseTable <- do.call(rbind, lapply(catVars, function(x){
  strTable(r, x)
}))

4.2.1 Categorical response table

A simple table to preview the values in the data. The values are ranked by frequency.

kable(responseTable)

variable	responses
metadatauserid	c3e5e4d69726a6587d9d5739f3961b03, ab7675956342e27f3a134b45731ca6f9, a8f48eb2ccc435935cdefec31a49f512, 2da910f9aa814b352b62821db7ac30fc, 7e1b7bc7a7147b9f4ddfedab54e8e470, 43ab9369b7e43edaa7d9614594f4d1dd, 9938a37f596038d85181e4d38cff2433, bfb7f31368600aefe2c4386ad49c5126, 4a69416450e53b6e762ea707aaf80104, 089ae26df7d5ea3886dbbe3709c34013
metadatausername	umushakashatsi, umushakashatsi3, umushakashatsi72, umushakashatsi42, umushakashatsi58, umushakashatsi14, umushakashatsi66, umushakashatsi7, umushakashatsi13, umushakashatsi73
metadatatimestart	2012-01-01T02:07:31.468000, 2012-01-01T21:53:26.687000, 2012-01-01T23:04:56.746000, 2012-01-06T20:14:52.707000, 2012-01-06T21:14:58.517000, 2012-01-07T01:08:44.167000, 2016-07-27T07:53:43.734000, 2016-07-27T08:39:53.902000, 2016-07-27T08:39:57.777000, 2016-07-27T08:41:57.353000
metadatatimeend	2012-01-06T20:52:59.887000, 2012-01-07T19:01:49.301000, 2012-01-07T19:04:31.323000, 2012-01-07T19:09:38.384000, 2016-07-27T09:41:47.415000, 2016-07-27T09:57:48.152000, 2016-07-27T10:43:47.085000, 2016-07-27T11:24:53.338000, 2016-07-27T11:25:03.144000, 2016-07-27T11:26:55.594000
start_time	09:00:00.000+02, 08:30:00.000+02, 09:40:00.000+02, 10:13:00.000+02, 10:36:00.000+02, 12:20:00.000+02, 09:14:00.000+02, 09:29:00.000+02, 10:14:00.000+02, 10:56:00.000+02
date	2016-08-10, 2016-08-11, 2016-08-08, 2016-08-17, 2016-08-03, 2016-08-18, 2016-08-22, 2016-08-19, 2016-08-04, 2016-08-12
enum_name	Hagenimana bienvenue, MUCYOWIMIHIGO J MV, Nyandwi Anathalie, ZIMUKWIYE Dominique, Nyirangirimana jeanne, Torero pacifique, Utamuriza Jeanne, Niyidufasha nathanael, Rukundo japhet, NYIRAMPANO Bernadette
photo	, 1325376816129.jpg, 1325447804135.jpg, 1325452024080.jpg, 1325873951716.jpg, 1325877535600.jpg, 1325891580194.jpg, 1469601919598.jpg, 1469601990645.jpg, 1469602247216.jpg
district	Rutsiro, Karongi, Mugonero, Nyamasheke, Huye, Rwamagana, Gatsibo_NLWH, Gatsibo_LWH, Nyamagabe, Kayonza
cell_field	Rubumba, Mubuga, Nyabicwamba, NYAGATARE, Mugera, MutongoCA, Bihumbe, Busetsa, Gihumuza, Kibyagira A
village	Gasharu, Murambi, Rugarama, Kabeza, Karambo, Kigarama, Nyabugogo, Kabuga, Kivumu, Gasagara
farmer_list	Havugimana celestin, Karekezi Celestin, Mukabinyange cecile, Mukafundi Marie, Musabyimana Jean, Ndananiwe Francois, Ndayambaje Emmanuel, Nsengiyumva Augustin, Nyirahabimana seraphine, Nyiraminani Constasie
farmer_respond	NA, Akimana Jeannette, BIMENYANDE Djumapri, Habimana Emmanuel, Hagumagatsi Gaspard, Karekezi Celestin, Mukabinyange cecile, Mukangiriye Donatha, Mukankusi Beatrice, MUNYENSANGA Emmanuel
farmer_phonenumber	NA, Ntayo, 0, ntayo, Nta telephone afite, Ntayo afite, 0.0, -, nta telephone afite, Ntayo bafite
d_phone	NA, 0, Ntayo, ntayo, Ni wewabajijwe, -, Ntayo afite, O, Nta telephone afite, Ntayo bafite
neighbor_phonenumber	NA, ntayo, 0, Ntayo, 0.0, -, 0789699430, 0785275883, 7.85275883E8, 0723071668
gender	female, male
n_tubura_season	not_a_client_3seasons, 16a 16b 17a, 16a 17a, 17a, 16a 16b, 16a, NA, 16b 17a, 16b, 16a not_a_client_3seasons
which_crop_16a_1	gor
which_maize_seed_16a_1	NA, gor_nsp, new_hybrid, OPV_saved, Hybride_saved, OPV_new
which_crop_16a_2	NA, yum, gor, big, insina, jum, soya, ray, shy, shaz
which_maize_seed_16a_2	NA, gor_nsp, Hybride, OPV_saved, OPV_new, Hybride_saved
fert_type1_16a	None, DAP, NA, NPK-17, urea, NPK-22, npk2555
fert_type2_16a	NA, urea, None, DAP, NPK-17, NPK-22, npk2555
quality_compost_16a	Good, NA, Average, Bad
type_compost_16a	cow, NA, goat, pig, other, plant, kitchen_waste, human, chicken
d_lime_16a	no_lime, NA, lime_outside, lime_tubura, both_tubura_non_tubura
which_crop_16b_1	big, shy, saka, NA, jum, soya, gor, ray, nyo, yum
which_maize_seed_16b_1	NA, new_hybrid, gor_nsp, OPV_new, Hybride_saved, OPV_saved
which_crop_16b_2	NA, gor, yum, jum, insina, big, soya, saka, shy, ray
which_maize_seed_16b_2	NA, new_hybrid, OPV_new, gor_nsp, Hybride_saved, OPV_saved
fert_type1_16b	None, NA, DAP, NPK-17, urea, NPK-22, npk2555
fert_type2_16b	NA, None, urea, DAP, NPK-17
quality_compost_16b	NA, Good, Average, Bad
type_compost_16b	NA, cow, pig, goat, kitchen_waste, plant, human, other, chicken
d_lime_16b	no_lime, NA, lime_outside, lime_tubura
how_use_residues	feed_animals, mulching, leave_field, compost_use, burn_field, burn_discard, sell
field_texture	clay_loam, loam, silty_clay_loam, sandy_clay_loam, sandy_loam, silty_loam, silty_clay, loamy_sand, sand, clay
field_erosion	drainageditch, nothing, radicalterrace, gradualterrace
crop_direction	not_applicable, NA, across_slope, down_slope
comments	, Ntakibazo, ntakibazo, ntayo, Ntayo, Ntazo, ntazo, Ntakibazo., Ntacyahindutse, NA
sample_id	12, 137, 1503, 2044C, 2278, 2299, 2610, 2612, 2612C, 10
kg_yield_hwag_16b_1	NA
kg_seed_ananas_16b_2	NA
kg_seed_veg_16a_1	NA
kg_seed_16a_1	N, 1, 0, 2, -, 3, 4, 5, 6, 8
kg_seed_16a_2	, NA, 0.5, 1.0, 0.25, 2.0, 3.0, 1.5, 4.0, 5.0
kg_seed_16b_1	NA, , 3.0, 2.0, 1.0, 0.5, 1.5, 4.0, 5.0, 6.0
kg_seed_16b_2	, NA, 0.5, 1.0, 0.25, 2.0, 1.5, 3.0, 4.0, 5.0
kg_yield_16a_1	NA, 50.0, 20.0, 100.0, 30.0, 10.0, 40.0, 15.0, 200.0, 5.0
kg_yield_16a_2	, NA, 20.0, 10.0, 50.0, 30.0, 0.0, 15.0, 5.0, 100.0
kg_yield_16b_1	, NA, 20.0, 30.0, 10.0, 15.0, 5.0, 50.0, 40.0, 100.0
kg_yield_16b_2	, NA, 0.0, 10.0, 5.0, 20.0, 15.0, 3.0, 40.0, 50.0
gps_coord	NA, , -1.5578864555610237 30.39436791689242 1525.93 15.0, -1.5631940702424174 30.227211802604916 1659.67 15.0, -1.5639320092237632 30.227385933820276 1434.79 10.0, -1.5667398240763533 30.273551799148027 979.26 10.0, -1.567033053159622 30.277914044142907 982.39 10.0, -1.5671285398447943 30.275353919885177 560.94 10.0, -1.5685424850437755 30.248542080122405 1468.14 20.0, -1.5688621725334673 30.24841864727349 851.74 10.0
unique_location	Gatsibo_NLWH2610, Gatsibo_NLWH2612, Gatsibo_NLWH2612C, Huye137, Karongi1503, Rutsiro2044C, Rutsiro2278, Rutsiro2299, Gatsibo_LWH2476, Gatsibo_LWH2476C

4.2.2 Categorical response graphs

repGraphs <- function(dat, x){
  tab = as.data.frame(table(dat[,x], useNA = 'ifany'))
  tab = tab[order(tab$Freq, decreasing = T),]
  print(
    ggplot(data=tab, aes(x=Var1, y=Freq)) + geom_bar(stat="identity") +
      theme(legend.position = "bottom", axis.text.x = element_text(angle = 45, hjust = 1)) +
      labs(title =paste0("Composition of variable: ", x))
  )
}
adminVars <- c(names(r)[grep("meta", names(r))], "start_time", "enum_name", "photo", "cell_field", "village", "farmer_respond", "farmer_phonenumber", "d_phone", "neighbor_phonenumber", "farmer_list", "unique_location", "comments", "gps_coord", "sample_id")
nonAdminVars <- catVars[!catVars %in% adminVars]
for(i in 1:length(nonAdminVars)){
  repGraphs(r, nonAdminVars[i])
}

4.2.3 Manual character cleaning

r$female <- ifelse(r$gender=="female", 1, 0)
r$district <- ifelse(grepl("nyanza", r$district)==T, "Nyanza", r$district)
table(r$kg_seed_16b_1)

        0.0 0.125  0.25   0.3   0.4   0.5  0.75   1.0  1.25   1.5  10.0  10.5 100.0  12.0  12.5 120.0  13.0  14.0  15.0 150.0  16.0  18.0  19.0  19.5 
  354     1     3    18     1     1   148     3   245     2   140    41     5     3    15     1     2     1     4    21     1     1     2     1     1 
  2.0   2.5  20.0 200.0  21.0  22.0  22.5  24.0  25.0   3.0   3.5  3.75  30.0 300.0  33.0  35.0  37.5  39.0   4.0   4.5  40.0  45.0   5.0   5.5  50.0 
  248    24    12     1     2     1     1     1     8   261     4     1    12     1     1     1     1     1    89    54     5     1    70     1     5 
500.0   6.0   6.5  60.0   7.0   7.5  70.0   8.0  80.0   9.0  9.75 
    1    68     3     1    26    23     3    30     1    16     1 

table(r$kg_yield_16a_2)

          0.0   0.25    0.5    1.0    1.5   10.0  100.0 1000.0  105.0   11.0   12.0  120.0 1250.0   13.0   15.0   15.5  150.0   16.0   16.5  165.0   17.0 
  1352     47      1      4     11      3     63     31      1      2      2      9      5      1      2     45      1      7      2      1      1      1 
   2.0    2.5   20.0  200.0   22.0   22.5   23.0   24.0  240.0   25.0   26.0   28.0    3.0   30.0  300.0   32.0   35.0  350.0   36.0   37.5  375.0   39.0 
    20      2     79      9      1      7      1      1      1     11      1      1     29     53      4      2      1      1      1      1      1      1 
   4.0    4.5   40.0  400.0   45.0   48.0    5.0   50.0  500.0   53.0   55.0    6.0    6.5   60.0  600.0  630.0    7.0    7.5   70.0   75.0    8.0   80.0 
    16     12     24      4      3      2     43     63      1      1      1     12      1     18      1      1      7     11      7      1     11     11 
  86.0    9.0   90.0 
     1      5      4 

strtoNum <- c("kg_seed_16b_1", "kg_yield_16a_1", "kg_yield_16b_1", "kg_yield_16b_2")
r[,strtoNum] <- sapply(r[,strtoNum], function(x){as.numeric(x)})

Notes on the categorical variables:

• We don’t have many actual responses on seed type despite all farmers telling us about a crop they are growing. Why? Check that there wasn’t a mislabeling of variables.
• Check the ‘which_maize_seed’ variables to make certain they’re flexible to the type of crop selected in the previous question.
• Confirm that blank is NA not 0.

4.3 Numeric variables

numVars <- names(r)[sapply(r, function(x){
  is.numeric(x)
})]

Basic cleaning of known issues like enumerator codes for DK, NWR, etc.

enumVals <- c(-88,-85, -99)
r[,numVars] <- sapply(numVars, function(y){
  r[,y] <- enumClean(r,y, enumVals)
})

4.3.1 Numeric outlier table

iqr.check <- function(dat, x) { 
  q1 = summary(dat[,x])[[2]]
  q3 = summary(dat[,x])[[5]] 
  iqr = q3-q1
  mark  = ifelse(dat[,x] < (q1 - (1.5*iqr)) | dat[,x] > (q3 + (1.5*iqr)), 1,0)
  tab = rbind(
    summary(dat[,x]),
    summary(dat[mark==0, x])
  )
  return(tab)
}
# remove admin vars
numAdminVars <- c(numVars[1:3])
numVarsNotAdmin <- numVars[!numVars %in% numAdminVars]
iqrTab <- do.call(plyr::rbind.fill, lapply(numVarsNotAdmin, function(y){
  #print(y)
  res = iqr.check(r, y)
  #print(dim(res))
  out = data.frame(var=rbind(y, paste(y, ".iqr", sep="")), res)
  return(out)
}))
iqrTab[,2:8] <- sapply(iqrTab[,2:8], function(x){round(x,1)})

The outlier table summarizes the numeric variables with and without IQR outliers to show how the data changes based on this filter.

knitr::kable(iqrTab, row.names = F, digits = 0, format = 'html')

var	Min.	X1st.Qu.	Median	Mean	X3rd.Qu.	Max.	NA.s
d_client_16b	0	0	0	0	1	1	NA
d_client_16b.iqr	0	0	0	0	1	1	NA
d_client_17a	0	0	0	0	1	1	NA
d_client_17a.iqr	0	0	0	0	1	1	NA
age	16	35	45	47	57	90	NA
age.iqr	16	35	45	47	57	90	NA
n_household	0	4	5	5	7	39	NA
n_household.iqr	0	4	5	5	7	11	NA
n_cows	0	0	1	1	1	15	NA
n_cows.iqr	0	0	1	1	1	2	NA
n_goats	0	0	0	1	2	18	NA
n_goats.iqr	0	0	0	1	2	5	NA
n_chickens	0	0	0	1	1	40	NA
n_chickens.iqr	0	0	0	0	0	2	NA
n_pigs	0	0	0	0	1	11	NA
n_pigs.iqr	0	0	0	0	1	2	NA
n_sheep	0	0	0	0	0	35	NA
n_sheep.iqr	0	0	0	0	0	0	NA
field_length	0	13	20	26	32	214	NA
field_length.iqr	0	13	20	23	30	60	NA
field_width	0	12	20	24	31	160	NA
field_width.iqr	0	12	20	22	30	59	NA
n_spots	3	3	3	4	5	5	NA
n_spots.iqr	3	3	3	4	5	5	NA
fert_kg1_16a	0	1	2	4	5	80	1408
fert_kg1_16a.iqr	0	1	2	3	4	11	1408
fert_kg2_16a	0	0	0	2	2	200	1198
fert_kg2_16a.iqr	0	0	0	1	2	5	1198
d_compost_16a	0	1	1	1	1	1	271
d_compost_16a.iqr	1	1	1	1	1	1	271
kg_compost_16a	0	100	200	268	300	20000	613
kg_compost_16a.iqr	0	100	191	205	300	600	613
kg_lime_16a	0	15	40	66	100	500	2345
kg_lime_16a.iqr	0	10	25	52	100	150	2345
fert_kg1_16b	0	1	2	4	4	100	1964
fert_kg1_16b.iqr	0	1	2	2	3	8	1964
fert_kg2_16b	0	0	0	0	0	88	1656
fert_kg2_16b.iqr	0	0	0	0	0	0	1656
d_compost_16b	0	0	1	0	1	1	529
d_compost_16b.iqr	0	0	1	0	1	1	529
kg_compost_16b	0	100	160	238	300	10000	1411
kg_compost_16b.iqr	0	100	150	193	250	600	1411
kg_lime_16b	1	10	25	59	50	650	2353
kg_lime_16b.iqr	1	10	25	32	50	100	2353
field_slope	-5	3	6	9	14	60	NA
field_slope.iqr	-5	3	6	9	14	30	NA
field_n_crops	0	1	1	2	2	30	343
field_n_crops.iqr	0	1	1	1	2	3	343
kg_seed_16b_1	0	1	2	5	4	500	754
kg_seed_16b_1.iqr	0	1	2	3	4	10	754
kg_yield_16a_1	0	15	34	73	80	6000	1570
kg_yield_16a_1.iqr	0	12	30	41	50	170	1570
kg_yield_16b_1	0	8	20	53	50	6000	600
kg_yield_16b_1.iqr	0	8	20	28	40	112	600
kg_yield_16b_2	0	3	10	25	25	600	1954
kg_yield_16b_2.iqr	0	3	8	13	20	55	1954
yield_compare_16a_1	1	1	1	2	3	3	1506
yield_compare_16a_1.iqr	1	1	1	2	3	3	1506
yield_compare_16a_2	1	1	2	2	2	3	1355
yield_compare_16a_2.iqr	1	1	2	2	2	3	1355
yield_compare_16b_1	1	1	1	2	2	3	358
yield_compare_16b_1.iqr	1	1	1	2	2	3	358
yield_compare_16b_2	1	1	1	2	2	3	1734
yield_compare_16b_2.iqr	1	1	1	2	2	3	1734
lat	-3	-2	-2	-2	-2	-2	497
lat.iqr	-3	-2	-2	-2	-2	-2	497
lon	29	29	30	30	30	31	497
lon.iqr	29	29	30	30	30	31	497
alt	-108	1513	1673	1668	1887	2668	497
alt.iqr	957	1541	1680	1728	1887	2430	497
precision	5	10	15	19	15	4181	497
precision.iqr	5	10	15	13	15	20	497
female	0	0	1	1	1	1	NA
female.iqr	0	0	1	1	1	1	NA

4.3.2 Outlier Graphs

# http://rforpublichealth.blogspot.com/2014/02/ggplot2-cheatsheet-for-visualizing.html
for(i in 1:length(numVarsNotAdmin)){
    base <- ggplot(r, aes(x=r[,numVarsNotAdmin[i]])) + labs(x = numVarsNotAdmin[i])
    temp1 <- base + geom_density()
    temp2 <- base + geom_histogram()
    #temp2 <- boxplot(r[,numVars[i]],main=paste0("Variable: ", numVars[i]))
    multiplot(temp1, temp2, cols = 2)
}

4.4 Check for unique ids

I’m seeing that there are duplicated farmers in the data when I’m trying to reshape the r data from wide to long. Let’s check them out here and see if we can figure out which observation is right.

• Check Alex’s do file to see if there’s mention of these farmers. [No mention]
• Check the baseline values as these should line up.

length(r$sample_id)==length(unique(r$sample_id))

[1] FALSE

dups <- r$sample_id[duplicated(r$sample_id)]
dupIndex <- which(duplicated(r$sample_id))
#dupDat <- r[r$sample_id %in% dups,]
head(r[r$sample_id==dups[1],])

head(r[r$sample_id==dups[2],])

Let’s solve the unique id issue by looking at identifying information in the baseline data

roundId <- r %>%
  dplyr::select(district, cell_field, village, sample_id, farmer_list) %>%
  filter(r$sample_id %in% dups)
#d
load("rawBaselineWithIdentifers.Rdata")
baseId <- d %>% 
  dplyr::select(district, selected_cell, umudugudu,  sample_id, farmer_name ) %>%
  filter(d$sample_id %in% dups)
#baseId
roundId

4.4.1 Correct duplicates

Correct the duplicates I can and drop the others for now. Flag the duplicated ones and save them to share with Nathaniel.

TODO(mattlowes) - share any remaining duplicates with Nathaniel and see if he has a solution. Also see if he can understand why this might have happened and if they should actually have a different sample id.

r <- r %>% mutate(
    dup = ifelse(
      sample_id == "12" & cell_field == "MUNANIRA" |
      sample_id == "137" & village == "Rusuma" |
      sample_id == "1503" & farmer_list=="NAKAGIZE Val\\xc3\\xa9rie" |
      #sample_id == "2044C" &  # same!
      sample_id == "2278" & cell_field=="Nkira A" | # check this as maybe this was the only thing wrong?
      #sample_id == "2299" & # same!
      sample_id == "2610" & village=="agakiri" #|  #agakiri is close to gakiri in spelling. Is this just a typo?
      #sample_id == "2612" &  # same names!
      #sample_id == "2612C" # same names!
      , 1, 0)
) %>% filter(
  dup!=1
) %>% dplyr::select(-dup) 
# run this code again from above to get updated duplicates list
#length(r$sample_id)==length(unique(r$sample_id))
dups <- r$sample_id[duplicated(r$sample_id)]
dupIndex <- which(duplicated(r$sample_id))
# for the time being drop the observations that are duplicates
r <- r[!r$sample_id %in% dups,]

4.5 Reshape variables

This should include the baseline variables as well.

Let’s first check with the baseline data to see what variables we made there so I can make the same ones from the round 1 data. There are some variables that are baseline variables only like variables asking about historical practices. There are then other variables that will vary by season. These are the variables that we ultimately want in to shape in a long dataset by season to analyze changes overtime in practices and soil management. I think this will result in a dataset that has one row per farmer per season. Some variables may not fit nicely into this but we can deal with those. For variables that aren’t changing over time they’ll show as not important in our model. They’re important for matching farmers.

There are a lot of variables to try to line up. Some already have the same name but how to best combine the ones that have different variable names? I’m going to write a function that takes a variable name from b and a variable name from r that should go together, updates the r variable name and uses that info to rbind the data into a long dataset.

# names(b)
# names(r)
# check the names that already match
baselineFound <- names(b)[names(b) %in% names(r)] # not many variable names are aligned

Update variable names so that any variable with 16a or 16b has a the a or b season designation at the end it so I can replicate the gather() and spread() options for reorganizing the data by season and by plot. This means that the variable names will retain their designation of first or second application and be distinguishable.

TODO(mattlowes) - rename the variables according to that convention to reshape the r data. Keep the baseline data in mind as we’ll want to do the same thing with the baseline data to make them match.

r <- r %>% rename(
  which_crop_1_16a = which_crop_16a_1,
  which_maize_seed_1_16a = which_maize_seed_16a_1,
  which_crop_2_16a = which_crop_16a_2,
  which_maize_seed_2_16a = which_maize_seed_16a_2,
  kg_seed_veg_1_16a = kg_seed_veg_16a_1,
  kg_seed_1_16a = kg_seed_16a_1,
  kg_seed_2_16a = kg_seed_16a_2,
  kg_yield_1_16a = kg_yield_16a_1,
  kg_yield_2_16a = kg_yield_16a_2,
  yield_compare_1_16a = yield_compare_16a_1,
  yield_compare_2_16a = yield_compare_16a_2,
  
  which_crop_1_16b = which_crop_16b_1,
  which_maize_seed_1_16b = which_maize_seed_16b_1,
  which_crop_2_16b = which_crop_16b_2,
  which_maize_seed_2_16b = which_maize_seed_16b_2,
  #kg_seed_veg_1_16a = kg_seed_veg_16a_1,
  kg_seed_1_16b = kg_seed_16b_1,
  kg_seed_2_16b = kg_seed_16b_2,
  kg_yield_1_16b = kg_yield_16b_1,
  kg_yield_2_16b = kg_yield_16b_2,
  yield_compare_1_16b = yield_compare_16b_1,
  yield_compare_2_16b = yield_compare_16b_2
)
aSeason <- names(r)[grep("16a", names(r))]
bSeason <- names(r)[grep("16b", names(r))]
seasonalVars <- rbind(aSeason, bSeason, "sample_id")
farmerVars <- names(r)[!names(r) %in% seasonalVars]

# example data
# df <- data.frame(
#   id = 1:10,
#   time = as.Date('2009-01-01') + 0:9,
#   Q3.2.1. = rnorm(10, 0, 1),
#   Q3.2.2. = rnorm(10, 0, 1),
#   Q3.2.3. = rnorm(10, 0, 1),
#   Q3.3.1. = rnorm(10, 0, 1),
#   Q3.3.2. = rnorm(10, 0, 1),
#   Q3.3.3. = rnorm(10, 0, 1)
# )
# 
# df %>%
#   gather(key, value, -id, -time) %>%
#   extract(key, c("question", "loop_number"), "(Q.\\..)\\.(.)") %>%
#   spread(question, value)

source("../oaflib/misc.R")
# aDat <- r[,names(r) %in% aSeason] # works for this too!
# aDat <- aDat[,grep("16a_1", names(aDat))] # works for this
aDat <- r[,names(r) %in% seasonalVars] # works for this!
#http://stackoverflow.com/questions/25925556/gather-multiple-sets-of-columns
seasonalDat <- aDat %>%
  gather(key, value, -sample_id) %>%
  tidyr::extract(key, c("variable", "season"), "(^.*\\_1.)(.)") %>%
  mutate(season = paste0("16", season)) %>% 
  spread(variable, value)
names(seasonalDat) <- gsub("_16", "", names(seasonalDat))

TODO(mattlowes) - confirm that the tidyr process worked as I expected as there are numerous missing values. These seem to appear where the variable only had one version of the variable, _16, rather than a _16a and a _16b. Check out how this is handling variables with _17 instead of _16.

4.6 Merge seasonal and demographic data

rs <- left_join(seasonalDat, r[,!names(r) %in% seasonalVars], by="sample_id")

Error: 'sample_id' column not found in lhs, cannot join

4.7 Create new variables

4.7.1 Field variables

rs$dim <- rs$field_length * rs$field_width
inputVars <- names(rs)[grep("fert_|quality_compost|type_compost|which_crop|which_maize", names(rs))]
rs[,inputVars] <- sapply(rs[, inputVars], tolower)
# input quanitites
rs$fert_kg_urea1 <- ifelse(rs$fert_type1=="urea", rs$fert_kg1, NA)
rs$fert_kg_urea2 <- ifelse(rs$fert_type2=="urea", rs$fert_kg2, NA)
rs$fert_total_urea <- apply(rs[, grep("(urea.)", names(rs))], 1, function(x){
  sum(as.numeric(x), na.rm=T)})
rs$fert_kg_dap1 <- ifelse(rs$fert_type1=="dap", rs$fert_kg1, NA)
rs$fert_kg_dap2 <- ifelse(rs$fert_type2=="dap", rs$fert_kg2, NA)
rs$fert_total_dap <- apply(rs[, grep("(dap.)", names(rs))], 1, function(x){
  sum(as.numeric(x), na.rm=T)})
rs$fert_kg_17npk1 <- ifelse(rs$fert_type1=="npk-17", rs$fert_kg1, NA)
rs$fert_kg_17npk2 <- ifelse(rs$fert_type2=="npk-17", rs$fert_kg2, NA)
rs$fert_total_npk17 <- apply(rs[, grep("(17npk.)", names(rs))], 1, function(x){
  sum(as.numeric(x), na.rm=T)})
rs$fert_kg_22npk1 <- ifelse(rs$fert_type1=="npk-22", rs$fert_kg1, NA)
rs$fert_kg_22npk2 <- ifelse(rs$fert_type2=="npk-22", rs$fert_kg2, NA)
rs$fert_total_npk22 <- apply(rs[, grep("(22npk.)", names(rs))], 1, function(x){
  sum(as.numeric(x), na.rm=T)})
rs$fert_kg_2555npk1 <- ifelse(rs$fert_type1=="npk2555", rs$fert_kg1, NA)
rs$fert_kg_2555npk2 <- ifelse(rs$fert_type2=="npk2555", rs$fert_kg2, NA)
rs$fert_total_npk2555 <- apply(rs[, grep("(2555npk.)", names(rs))], 1, function(x){
  sum(as.numeric(x), na.rm=T)})

4.7.2 Demographic variables

rs$season_16a <- ifelse(grep("16a", rs$n_tubura_season), 1, 0)

Error in `$<-.data.frame`(`*tmp*`, "season_16a", value = c(1, 1, 1, 1,  : 
  replacement has 2204 rows, data has 4762

4.8 Combine long with baseline

The matchRounds function updates variable names across rounds and reports the index and new name of the variables. I can then take the first part of the list for dat1 and the second part for dat2.

matchRounds <- function(dat1, dat2, var1, var2, new=NULL, choice="first"){
  
  
  
  if (choice=="first"){
    var2new  = var1
    #names(dat2)[names(dat2)==var2] <- var2new
    return(list(
      list(var1, grep(var1, names(dat1))),
      list(var2new, grep(var2, names(dat2)))
                ))
    
  } else if (choice=="second") {
    var1new = var2
    #names(dat1)[names(dat1)==var1] <- var1new
    return(list(
      list(var1new, grep(var1, names(dat1))),
      list(var2, grep(var2, names(dat2)))
                ))
    
  } else{
    var1new = var2new = new
    #names(dat2)[names(dat2)==var2] <- var2new 
    #names(dat1)[names(dat1)==var1] <- var1new
    return(list(
      list(var1new, grep(var1, names(dat1))),
      list(var2new, grep(var2, names(dat2)))
                ))
  }
} 


namesToUpdate <- rbind(
 c("demographicdate", "date", "first"),
  c("sample", "d_sample", "second")
)


# example
dat1=b
dat2=r
var1 = "field_dim1"
var2 = "field_length"
choice="first"

test <- matchRounds(b, r, "field_dim1", "field_length", choice="first")
test2 <- matchRounds(b, r, "field_dim2", "field_width", choice="first")


test <- lapply(namesToUpdate, function(x,y,z){
  val = matchRounds(b, r, x, y, choice=z)
  return(val)
})

5 Analysis

5.1 Demographic summary

5.2 Soil summary

5.3 Longitudinal soil summary

5.4 Matching

5.5 Regressions

6 Summary

6.1 Changes to the survey

7 Appendix